package com.turing.post.cardmanagement.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.InitialInStockOrder;
import com.turing.post.cardmanagement.util.InitialInStockOrderDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 入库单单管理
 * 
 * @author YangShuo
 * 
 */
@Stateless(mappedName = "cardmanagement.impl.InitialInStockOrderDaoImpl")
@Remote
@Local
public class InitialInStockOrderDaoImpl implements InitialInStockOrderDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * @根据主键查询
	 * @param InitialInStockOrderId
	 * @return InitialInStockOrder
	 */
	@Override
	public InitialInStockOrder getInitialInStockOrderByCardInitialInStockOrderId(
			BigInteger InitialInStockOrderId) {
		return _em.find(InitialInStockOrder.class, InitialInStockOrderId);
	}

	/**
	 * @新增记录
	 * @param InitialInStockOrder
	 */
	@Override
	public void insertInitialInStockOrder(
			InitialInStockOrder InitialInStockOrder)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, InitialInStockOrder);
	}

	/**
	 * 制卡入库列表
	 * 
	 * @param input
	 * @param startDate
	 * @param endDate
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<InitialInStockOrder> getInitialInStockOrderList(
			PCPSWebInput input, String startDate, String endDate,
			String status, Integer page) {
		if (input == null || PCPSUtil.isEmpty(input.getOperBranchNo())) {
			return null;
		}
		String startDateCond = "";
		if (!PCPSUtil.isEmpty(startDate)) {
			startDateCond = " and t.InstockTime>=to_date('" + startDate
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		String endDateCond = "";
		if (!PCPSUtil.isEmpty(endDate)) {
			endDateCond = " and t.InstockTime<=to_date('" + endDate
					+ " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		String sql = "select * from InitialInStockOrder t where t.IssueBranchNo=?1"
				+ startDateCond + endDateCond;
		if (InitialInStockOrder.InStockOrder_Status_Wait.equals(status)) {
			sql += " and (status='"
					+ InitialInStockOrder.InStockOrder_Status_InputWait
					+ "' or status='"
					+ InitialInStockOrder.InStockOrder_Status_OutWait + "')";
		} else {
			if (!PCPSUtil.isEmpty(status)) {
				sql += "and status='" + status + "'";
			}
		}
		sql += "order by t.instocktime asc";
		Query query = null;
		if (page == -1)
			query = _em.createNativeQuery(sql, InitialInStockOrder.class);
		else {
			int maxResult = Config.PAGE_SIZE;
			int startPosition = maxResult * page;
			query = _em.createNativeQuery(sql, InitialInStockOrder.class)
					.setFirstResult(startPosition).setMaxResults(maxResult);
		}
		query.setParameter(1, input.getOperBranchNo());
		query.setParameter(2, status);
		return query.getResultList();
	}

	/**
	 * 制卡入库总数
	 * 
	 * @param input
	 * @param startDate
	 * @param endDate
	 * @param page
	 * @return
	 */
	public Integer getInitialInStockOrderTotal(PCPSWebInput input,
			String startDate, String endDate, String status) {
		if (input == null || PCPSUtil.isEmpty(input.getOperBranchNo())) {
			return 0;
		}
		String startDateCond = "";
		if (!PCPSUtil.isEmpty(startDate)) {
			startDateCond = " and t.InstockTime>=to_date('" + startDate
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		String endDateCond = "";
		if (!PCPSUtil.isEmpty(endDate)) {
			endDateCond = " and t.InstockTime<=to_date('" + endDate
					+ " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		String sql = "select nvl(count(*),0) from InitialInStockOrder t where t.IssueBranchNo=?1"
				+ startDateCond + endDateCond;
		if (InitialInStockOrder.InStockOrder_Status_Wait.equals(status)) {
			sql += " and (status='"
					+ InitialInStockOrder.InStockOrder_Status_InputWait
					+ "' or status='"
					+ InitialInStockOrder.InStockOrder_Status_OutWait + "')";
		} else {
			if (!PCPSUtil.isEmpty(status)) {
				sql += "and status='" + status + "'";
			}
		}
		sql += "order by t.instocktime asc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, input.getOperBranchNo());
		query.setParameter(2, status);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 已入库卡查询
	 * 
	 * @param batchNo
	 *            制卡批次号
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Integer[]> getInputCardSeqNoByBatchNo(String batchNo) {
		String sql = "select to_number(substr(t.cardstartseqno,13,18)) startseqno,to_number(substr(t.cardendseqno,13,18)) endseqno from InitialInStockOrder t where t.batchno='"
				+ batchNo + "' and t.status='1' order by startseqno";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		List<Integer[]> arrInt = new ArrayList<Integer[]>();
		Object[] obj = null;
		for (int i = 0; i < list.size(); i++) {
			obj = list.get(i);
			arrInt.add(new Integer[] { Integer.parseInt(obj[0].toString()),
					Integer.parseInt(obj[1].toString()) });
		}
		return arrInt;
	}

	/**
	 * @修改记录
	 * @param InitialInStockOrder
	 */
	@Override
	public void updateInitialInStockOrderForCancel(
			InitialInStockOrder InitialInStockOrder)
			throws PCPSApplicationException {
		String sql = "update INITIALINSTOCKORDER set cancelinstocktime=?1,status=?2 where INITIALINSTOCKORDERID=?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, InitialInStockOrder.getCancelInStockTime());
		query.setParameter(2, InitialInStockOrder.getStatus());
		query.setParameter(3, InitialInStockOrder.getInitialInStockOrderId());
		query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getInitialInStockOrderReportByCondition(
			PCPSWebInput webInput, String issueBranchNo, boolean isSingle,
			String startTime, String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(issueBranchNo))
			return null;
		String sqlInstockTime = " and i.instocktime >= to_date('" + startTime
				+ " 00:00:00','yyyy-MM-dd hh24:mi:ss') ";
		sqlInstockTime += " and i.instocktime <= to_date('" + endTime
				+ " 23:59:59','yyyy-MM-dd hh24:mi:ss') ";
		String sqlIssueBrchNo = "";
		if (isSingle) {
			sqlIssueBrchNo += " and i.issuebranchno = '" + issueBranchNo + "' ";
		} else {
			sqlIssueBrchNo += PCPSUtil.getSqlCond(webInput, issueBranchNo,
					"i.issuebranchno");
		}
		String sql = "select a.instocktime,a.issuebranchname,c.cardfacename,a.cardseqno,b.cardmanufacturename,case when c.MediaType= '0' then '磁卡' when c.mediatype= '1' then 'CPU多界面卡' end as mediatype,a.batchno,a.instockcount,a.OrderNumber from (select to_char(i.instocktime,'yyyy-mm-dd') instocktime,b.issuebranchname,(select t.cardfaceid from MakeCardControl t where t.batchno=i.batchno) cardfaceid,i.cardstartseqno||'至'||i.cardendseqno cardseqno,(select t.cardmanufactureid from MakeCardControl t where t.batchno=i.batchno) cardmanufactureid,i.batchno,i.instockcount,i.OrderNumber from initialinstockorder i,IssueBranch b where i.issuebranchno=b.issuebranchno and i.status='"
				+ InitialInStockOrder.InStockOrder_Status_Input
				+ "'"
				+ sqlIssueBrchNo + sqlInstockTime;
		sql += ")a,CardManufacture b,CardFace c where a.cardmanufactureid=b.cardmanufactureid and a.cardfaceid=c.cardfaceid order by a.batchno,a.InStockTime";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<InitialInStockOrder> getInitialInStockOrderListByCond(
			InitialInStockOrder iiso, String status) {
		String sql = "select * from initialinstockorder t where t.batchno=?1 and t.status=?2 and (?3 between t.cardstartseqno and t.cardendseqno or ?4 between t.cardstartseqno and t.cardendseqno)";
		Query query = _em.createNativeQuery(sql, InitialInStockOrder.class);
		query.setParameter(1, iiso.getBatchNo());
		query.setParameter(2, status);
		query.setParameter(3, iiso.getCardStartSeqNo());
		query.setParameter(4, iiso.getCardEndSeqNo());
		List<InitialInStockOrder> list = query.getResultList();
		return list;
	}
}
